﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_CAR_REPORT_Car2013r_Get_Yearly_Cost_List')
	BEGIN
		DROP Procedure dbo.usp_UPDMS_CAR_REPORT_Car2013r_Get_Yearly_Cost_List
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_CAR_REPORT_Car2013r_Get_Yearly_Cost_List
**	Desc : 연간차량비용집계 - 계정별,월별 합계 + 월별총계
**	Test Exec Query : Exec usp_UPDMS_CAR_REPORT_Car2013r_Get_Yearly_Cost_List 'CAR01', '2014-05', 'ko'
**	Called by : Car_Dac_UPDMS_CAR_REPORT_Car2013r.cs
**	Program ID : Car2013r
**	Auth : 송시명
**	Date : 2012-08-29
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_CAR_REPORT_Car2013r_Get_Yearly_Cost_List]
@ls_car_id nvarchar(5),
@ls_base_ym nvarchar(7),
@ls_lang_set nvarchar(2)

AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

;WITH rslt AS (
SELECT Div,
       Biz_Dt,
	   SUM(Amount) AS Amount
  FROM (
        --주유
        SELECT 'AH001' AS Div,
               LEFT(Refuelling_Dt, 7) AS Biz_Dt,
               SUM(Amount) AS Amount
          FROM UPDMS_CAR_REFUELLING AS ucr WITH(NOLOCK)
          JOIN UPDMS_CAR_STATION AS ucs WITH(NOLOCK) ON ucr.Station_Id = ucs.Station_Id
         WHERE ucr.Car_Id = @ls_car_id
           AND LEFT(ucr.Refuelling_Dt, 4) = LEFT(@ls_base_ym, 4)
         GROUP BY LEFT(ucr.Refuelling_Dt, 7)
         UNION ALL
        --정비
        SELECT 'AH002',
               LEFT(Service_Dt, 7),
               SUM(Amount)
          FROM UPDMS_CAR_SERVICE WITH(NOLOCK)
         WHERE Car_Id = @ls_car_id
           AND LEFT(Service_Dt, 4) = LEFT(@ls_base_ym, 4)
           AND Div <> 'AD003'
         GROUP BY LEFT(Service_Dt, 7)
         UNION ALL
        --주차,통행료(카드)
        SELECT 'AH003',
               LEFT(Biz_Dt, 7),
               SUM(Amount)
          FROM UPDMS_CRD_ITEM_BIZ WITH(NOLOCK)
         WHERE Account = 'AC024' 
           AND LEFT(Biz_Dt, 4) = LEFT(@ls_base_ym, 4)
         GROUP BY LEFT(Biz_Dt, 7)
         UNION ALL
        --주차,통행료(현금)
        SELECT 'AH003',
               LEFT(Reg_Dt, 7),
               SUM(Amount)
          FROM UPDMS_MNY_IN_OUT_MGT WITH(NOLOCK)
         WHERE Account = 'AC024'
           AND LEFT(Reg_Dt, 4) = LEFT(@ls_base_ym, 4)
         GROUP BY LEFT(Reg_Dt, 7)
        UNION ALL
        --세차
        SELECT 'AH004',
               LEFT(Service_Dt, 7),
               SUM(Amount)
          FROM UPDMS_CAR_SERVICE WITH(NOLOCK)
         WHERE Car_Id = @ls_car_id
           AND LEFT(Service_Dt, 4) = LEFT(@ls_base_ym, 4)
           AND Div = 'AD003'
         GROUP BY LEFT(Service_Dt, 7)
        ) x
 GROUP BY x.Div, x.Biz_Dt
 ) 

SELECT Div,
       dbo.ufn_UPDMS_Get_Code_Name(Div,'CAR',@ls_lang_set) AS Div_Nm,
       dbo.ufn_UPDMS_Convert_Money(CONVERT(NVARCHAR,ISNULL([M01], 0))) AS M01,
	   dbo.ufn_UPDMS_Convert_Money(CONVERT(NVARCHAR,ISNULL([M02], 0))) AS M02,
	   dbo.ufn_UPDMS_Convert_Money(CONVERT(NVARCHAR,ISNULL([M03], 0))) AS M03,
	   dbo.ufn_UPDMS_Convert_Money(CONVERT(NVARCHAR,ISNULL([M04], 0))) AS M04,
	   dbo.ufn_UPDMS_Convert_Money(CONVERT(NVARCHAR,ISNULL([M05], 0))) AS M05,
	   dbo.ufn_UPDMS_Convert_Money(CONVERT(NVARCHAR,ISNULL([M06], 0))) AS M06,
	   dbo.ufn_UPDMS_Convert_Money(CONVERT(NVARCHAR,ISNULL([M07], 0))) AS M07,
	   dbo.ufn_UPDMS_Convert_Money(CONVERT(NVARCHAR,ISNULL([M08], 0))) AS M08,
	   dbo.ufn_UPDMS_Convert_Money(CONVERT(NVARCHAR,ISNULL([M09], 0))) AS M09,
	   dbo.ufn_UPDMS_Convert_Money(CONVERT(NVARCHAR,ISNULL([M10], 0))) AS M10,
	   dbo.ufn_UPDMS_Convert_Money(CONVERT(NVARCHAR,ISNULL([M11], 0))) AS M11,
	   dbo.ufn_UPDMS_Convert_Money(CONVERT(NVARCHAR,ISNULL([M12], 0))) AS M12,
       dbo.ufn_UPDMS_Convert_Money(CONVERT(NVARCHAR,ISNULL([M01], 0)+ISNULL([M02], 0)+ISNULL([M03], 0)+ISNULL([M04], 0)+ISNULL([M05], 0)+ISNULL([M06], 0)+
       ISNULL([M07], 0)+ISNULL([M08], 0)+ISNULL([M09], 0)+ISNULL([M10], 0)+ISNULL([M11], 0)+ISNULL([M12], 0))) AS Total_Amt
  FROM
     (
       SELECT 'M'+RIGHT(umm.Month_Id, 2) AS Month_Id,
              rslt2.Div,
              rslt2.Amount
         FROM UPDMS_MST_MONTH AS umm WITH(NOLOCK)
         LEFT OUTER JOIN rslt AS rslt2 ON umm.Month_Id = rslt2.Biz_Dt
        WHERE umm.Month_Id >= LEFT(@ls_base_ym, 4) + '-01'
          AND umm.Month_Id <= LEFT(@ls_base_ym, 4) + '-12'
     ) AS pv
 PIVOT ( MAX(pv.Amount) FOR pv.Month_Id IN ([M01],[M02],[M03],[M04],[M05],[M06],[M07],[M08],[M09],[M10],[M11],[M12])
       ) AS pvt
 WHERE Div IS NOT NULL

GO
